﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Windows.Forms;

namespace ExcelDemo.Helper
{
    public class AsposeExcel
    {
        public static DataTable ReadExcel(string strFileName, int index)
        {
            if (string.IsNullOrEmpty(strFileName))
            {
                return null;
            }
            DataSet ds = new DataSet();
            Workbook book = new Workbook(strFileName);
            //book.Open(strFileName);
            Worksheet sheet = book.Worksheets[index];
            Cells cells = sheet.Cells;
            if (cells.MaxDataRow < 0)
            {
                return null;
            }
            ds.Tables.Add(cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true));
            return ds.Tables[0];
        }


        #region 根据Excel模板导出 string DataTable Dictionary<String,Object>

        public static void TemplateExportExcel(string templatePath, DataTable dtSource)
        {
            SaveFileDialog sf = new SaveFileDialog();
            sf.Filter = "Excel files (*.xlsx)|*.xlsx";
            string path = System.IO.Path.Combine(Application.StartupPath, templatePath);

            if (sf.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    Workbook book = new Workbook(templatePath);
                    //book.Open(templatePath);
                    Worksheet sheet = book.Worksheets[0];

                    //  写入数据的启示位置
                    string cell_start_region = "A3";
                    //  获得开始位置的行号                    
                    int startRow = sheet.Cells[cell_start_region].Row;
                    //  获得开始位置的列号  
                    int startColumn = sheet.Cells[cell_start_region].Column;
                   // DataRow[] foundRow = dtSource.Select("ID=99", "");
                    dtSource.Rows.RemoveAt(0);//移除标题行
                    //  写入Excel。参数说明，直接查阅文档
                    //int totalcount = sheet.Cells.ImportDataTable(dtSource, false, startRow, startColumn, true, true);
                    int totalcount = sheet.Cells.ImportDataTable(dtSource, false, startRow, startColumn, dtSource.Rows.Count, dtSource.Columns.Count, false, "yyyy/mm/dd");

                    //  保存文件
                    book.Save(sf.FileName, SaveFormat.Xlsx);
                    MessageBox.Show("导出成功！");

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "异常");
                }
            }
        }
        #endregion


        /// <summary>
        /// 下载文件(显示进度)
        /// </summary>
        /// <param name="URL"></param>
        /// <param name="filename"></param>
        /// <param name="prog"></param>
        public static void DownloadFile(string URL, string filename, System.Windows.Forms.ProgressBar prog)
        {
            try
            {
                System.Net.HttpWebRequest Myrq = (System.Net.HttpWebRequest)System.Net.HttpWebRequest.Create(URL);
                System.Net.HttpWebResponse myrp = (System.Net.HttpWebResponse)Myrq.GetResponse();
                long totalBytes = myrp.ContentLength;

                if (prog != null)
                {
                    prog.Maximum = (int)totalBytes;
                }

                System.IO.Stream st = myrp.GetResponseStream();
                System.IO.Stream so = new System.IO.FileStream(filename, System.IO.FileMode.Create);
                long totalDownloadedByte = 0;
                byte[] by = new byte[1024];
                int osize = st.Read(by, 0, (int)by.Length);
                while (osize > 0)
                {
                    totalDownloadedByte = osize + totalDownloadedByte;
                    System.Windows.Forms.Application.DoEvents();
                    so.Write(by, 0, osize);

                    if (prog != null)
                    {
                        prog.Value = (int)totalDownloadedByte;
                    }
                    osize = st.Read(by, 0, (int)by.Length);
                }
                so.Close();
                st.Close();
            }
            catch (System.Exception)
            {
                throw;
            }
        }


        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <param name="form"></param>
        /// <param name="callback"></param>
        public static void ImportExcelToDataTable(Form form, Action<string, List<DataTable>> callback, string SheetName = "Sheet1")
        {
            string Filter = "Excel文件|*.csv|Excel文件|*.xls|Excel文件|*.xlsx";

            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Title = "Excel文件";
            openFileDialog1.Filter = Filter;
            openFileDialog1.FilterIndex = 3;
            openFileDialog1.ValidateNames = true;
            openFileDialog1.CheckFileExists = true;
            openFileDialog1.CheckPathExists = true;

            if (openFileDialog1.ShowDialog() != DialogResult.OK)
                return;

            var action = new Action(() =>
            {
                List<DataTable> resultDT = new List<DataTable>();
                string localFilePath = openFileDialog1.FileName;
                //DataTable ds = AsposeExcel.ReadExcel(localFilePath, 0);
                if (File.Exists(localFilePath))
                {
                    string message = string.Empty;
                    string fileExten = Path.GetExtension(localFilePath);

                    DataTable dtExcel;
                    if (fileExten.ToLower().Contains(".csv"))
                    {
                        dtExcel = ImportCSVFile(localFilePath, "Table1", ref message);
                    }
                    else
                    {
                        //保存分数sheet
                        dtExcel = AsposeExcel.ReadExcel(localFilePath, 0);
                        resultDT.Add(dtExcel);
                        //保存规则sheet
                        dtExcel = AsposeExcel.ReadExcel(localFilePath, 1);
                        resultDT.Add(dtExcel);
                    }

                    if (callback != null)
                    {
                        if (form.InvokeRequired)
                        {
                            form.Invoke(callback, message, resultDT);
                        }
                        else
                        {
                            callback(message, resultDT);
                        }
                    }
                    string strPath = System.AppDomain.CurrentDomain.BaseDirectory + "TempFile\\";
                    bool flag = UploadFile(localFilePath, strPath, true);
                }
            });

            action.BeginInvoke(null, null);
        }



        /// <summary>
        /// 执行导入
        /// </summary>
        /// <param name="strFileName">对应文件路径</param>
        /// <param name="typeName">返回的Table名称</param>
        /// <param name="message">返回的错误</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportCSVFile(string strFileName, string typeName, ref string message)
        {

            if (string.IsNullOrEmpty(strFileName)) return null;

            string line = string.Empty;
            string[] split = null;
            bool isReplace;
            int subBegion;
            int subEnd;
            string itemString = string.Empty;
            string oldItemString = string.Empty;
            DataTable table = new DataTable(typeName);
            DataRow row = null;
            StreamReader sr = new StreamReader(strFileName, System.Text.Encoding.Default);
            //创建与数据源对应的数据列 
            line = sr.ReadLine();
            split = line.Split(',');
            foreach (String colname in split)
            {
                table.Columns.Add(colname, System.Type.GetType("System.String"));
            }
            //将数据填入数据表 
            int j = 0;
            while ((line = sr.ReadLine()) != null)
            {
                subEnd = 0;
                subBegion = 0;

                if (line.IndexOf('\"') > 0)
                {
                    isReplace = true;
                }
                else
                {
                    isReplace = false;
                }
                itemString = string.Empty;
                while (isReplace)
                {

                    subBegion = line.IndexOf('\"');
                    subEnd = line.Length - 1;
                    if (line.Length - 1 > subBegion)
                    {
                        subEnd = line.IndexOf('\"', subBegion + 1);
                    }

                    if (subEnd - subBegion > 0)
                    {
                        itemString = line.Substring(subBegion, subEnd - subBegion + 1);
                        oldItemString = itemString;
                        itemString = itemString.Replace(',', '|').Replace("\"", string.Empty);
                        line = line.Replace(oldItemString, itemString);

                    }

                    if (line.IndexOf('\"') == -1)
                    {
                        isReplace = false;
                    }

                }

                j = 0;
                row = table.NewRow();
                split = line.Split(',');
                foreach (String colname in split)
                {
                    row[j] = colname.Replace('|', ',');
                    j++;
                }
                table.Rows.Add(row);
            }
            sr.Close();
            //显示数据 
            return table;
        }



        /// <summary>
        /// WebClient上传文件至服务器
        /// site http://www.jbxue.com
        /// </summary>
        /// <param name="localFilePath">文件名，全路径格式</param>
        /// <param name="serverFolder">服务器文件夹路径</param>
        /// <param name="reName">是否需要修改文件名，这里默认是日期格式</param>
        /// <returns></returns>
        public static bool UploadFile(string localFilePath, string serverFolder, bool reName)
        {
            string fileNameExt, newFileName, uriString;
            if (reName)
            {
                fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf(".") + 1);
                newFileName = "ScoreTemp." + fileNameExt;
            }
            else
            {
                newFileName = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);
            }

            if (!serverFolder.EndsWith("/") && !serverFolder.EndsWith("\\"))
            {
                serverFolder = serverFolder + "/";
            }

            uriString = serverFolder + newFileName;   //服务器保存路径

            if (File.Exists(uriString))
            {
                File.Delete(uriString);
            }

            /// 创建WebClient实例
            WebClient myWebClient = new WebClient();
            myWebClient.Credentials = CredentialCache.DefaultCredentials;

            // 要上传的文件
            FileStream fs = new FileStream(localFilePath, FileMode.Open, FileAccess.Read);
            BinaryReader r = new BinaryReader(fs);
            try
            {
                //使用UploadFile方法可以用下面的格式
                //myWebClient.UploadFile(uriString,"PUT",localFilePath);
                byte[] postArray = r.ReadBytes((int)fs.Length);
                Stream postStream = myWebClient.OpenWrite(uriString, "PUT");
                if (postStream.CanWrite)
                {
                    postStream.Write(postArray, 0, postArray.Length);
                }
                else
                {
                    MessageBox.Show("文件目前不可写！");
                }
                postStream.Close();
            }
            catch
            {
                //MessageBox.Show("文件上传失败，请稍候重试~");
                return false;
            }

            return true;
        }

    }
}




